iT邦幫忙

2021 iThome 鐵人賽

DAY 7
1
Software Development

MYSQL-相關實務操作學習紀錄系列 第 7

Day.7 保有日常備份重要性 - binlog 解析 &備份資料 (mysqldump / binlog)

  • 分享至 

  • xImage
  •  

回顧一下上篇提到的binlog紀錄作用,可以用來做資料復原和主從機制下的重要數據紀錄。

開始前先了解一下如何使用<< mysqldump >>指令做數據庫備份

常用的幾個舉例如下:
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

(資料備份)
備份單一資料庫
# mysqldump -u root -p db_name > back_up.sql;
備份資料庫中一or多個資料表
# mysqldump -u root -p db_name tbl_name tbl_name2 > back_up.sql;
備份所有資料庫
# mysqldump -u root -p --all-databases > back_up.sql;

(資料還原)
復原多個資料庫
# mysql -u root -p < back_up.sql
復原單一資料庫
# mysql -u root -p database_name < back_up.sql

當然以上只是基礎的備份~還有像只備結構或數據等...參數使用參考文章。 mysqldump參數用法


有了平時的備份數據,當發生誤刪數據狀況時,如何藉由binlog紀錄找到發生點做到數據恢復?

  • 了解binlog大致內容。
  • 如何過濾binlog找到問題語法。
  • 發生誤刪動作時,如何透過平時備份數據復原。

了解binlog內容,利用 << mysqlbinlog >> 找events pos紀錄位置。

以下 2 種瀏覽binlog方式:

1.原始binlog紀錄檔 (擷取執行SQL: delete from members where id =5 的一段完整紀錄)
2.利用 Mysql cli 查詢binlog紀錄。

紀錄檔 mysql-bin.00000x

  • commit: 提交事件結束點。可以快速判斷下一個事件的開始位置
  • at xxx: 就是我們要找的事件位置~ex. at 456 =事件紀錄開始位置為456
  • GTID事件: 在5.7版本中,執行事務之前,都會紀錄一個GTID Event,用於主從同步複制。參考圖.2
  • end_log_pos: 當前事件結束位置。同時也是下一个事件开始的位置。 參考圖.2
  • 紀錄方式: 目前設定為row模式,所以可以看到當我執行delete語法時只帶了id=5的條件,但該筆資料的其他欄位資訊也會被完整紀錄。
  • #210810 2:59:55 server id 1 : 事件發生時間和服務標示ID
  • Delete_rows : 事件類型

圖.1

# at 425
#210810  2:38:58 server id 1  end_log_pos 456 CRC32 0xc26fb981 	Xid = 605
COMMIT/*!*/;       
# at 456   
#210810  2:59:55 server id 1  end_log_pos 521 CRC32 0x93934176 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;  
# at 521
#210810  2:59:55 server id 1  end_log_pos 593 CRC32 0x368339f5 	Query	thread_id=6	exec_time=0	error_code=0    
SET TIMESTAMP=1628564395/*!*/;
BEGIN
/*!*/;
# at 593
#210810  2:59:55 server id 1  end_log_pos 646 CRC32 0x799d4af6 	Table_map: `user`.`members` mapped to number 110  
# at 646
#210810  2:59:55 server id 1  end_log_pos 705 CRC32 0xfa28c00a 	Delete_rows: table id 110 flags: STMT_END_F

BINLOG '
q+sRYRMBAAAANQAAAIYCAAAAAG4AAAAAAAEABHVzZXIAB21lbWJlcnMAAwj8CAECAPZKnXk=
q+sRYSABAAAAOwAAAMECAAAAAG4AAAAAAAEAAgAD//gFAAAAAAAAAAUAc2lhbmdYYMUJAAAAAArA
KPo=
'/*!*/;
### DELETE FROM `user`.`members`  
### WHERE
###   @1=5
###   @2='siang'
###   @3=163930200
# at 705
#210810  2:59:55 server id 1  end_log_pos 736 CRC32 0xe959995d 	Xid = 607
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

圖.2
https://ithelp.ithome.com.tw/upload/images/20210810/20130880RU72VYfM67.png

指定POS查詢
mysql> show binlog events in 'mysql-bin.000001' from 425 limit 10;

不過在生產環境上資料是持續在異動的,所以log紀錄會很龐大,不會直接打開binlog檔去找操作紀錄位置。得透過一些過濾的查詢方便我們在找紀錄上快速定位。

ex. 查詢這句SQL指令POS位置 (delete from members where id =5 ) 的2種方式~

(一)藉由SQL指令關鍵字

  1. 從binlog檔案中找出關鍵字對應行數
    # mysqlbinlog -v mysql-bin.000001 | cat -n | grep -iw 'delete'   
查詢結果:  
    74	### DELETE FROM `user`.`members` 
  1. 藉由過濾後的內容上下文就能找到pos點做後續復原動作
    # mysqlbinlog -v mysql-bin.000001 | cat -n | sed -n '50,81p'

查詢結果:  
     50	###   @3=163930200

       ....

     62	BEGIN
       ....
     65	#210810  2:59:55 server id 1  end_log_pos 646 CRC32 0x799d4af6 	Table_map:             `user`.`members` mapped to number 110
     66	# at 646
     67	#210810  2:59:55 server id 1  end_log_pos 705 CRC32 0xfa28c00a 	Delete_rows:           table id 110 flags: STMT_END_F

       ....

     74	### DELETE FROM `user`.`members`
     75	### WHERE
     76	###   @1=5
     77	###   @2='siang'
     78	###   @3=163930200

       ....

     81	COMMIT/*!*/;

(二)藉由限制發生時間和指定資料庫

  # mysqlbinlog -v --start-datetime="2021-08-10 02:59:00" --database=user --base64-output=decode-rows mysql-bin.000001

下集預告: 知道備份和binlog紀錄怎麼使用後,明天來準備跑模擬流程的資料/images/emoticon/emoticon33.gif


上一篇
Day.6 留個紀錄好辦事 - Mysql Log (日誌紀錄)-下
下一篇
Day.8 備份還原 - 備份資料 (MYSQL binlog )-上
系列文
MYSQL-相關實務操作學習紀錄30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言